package com.yjz;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.core.io.ClassPathResource;

import java.io.IOException;
import java.io.InputStream;

public class SqlGenerate {

  public static void main(String[] args) throws IOException {

    String permissionType = "BUTTON";
    String createTime = "now()";
    String updateTime = "now()";
    String createBy = "手动导入";
    String updateBy = "手动导入";
    String deleted = "0";
    String endFlag = ");";

    //INSERT INTO quixmart_business.b_merchant_permission (parent_id, permission_code, permission_name, permission_type, create_time, update_time, create_by, update_by, deleted) VALUES (0, 'dashboard', '仪表盘', 'MENU', '2018-04-22 10:17:36', '2018-06-17 10:30:00', 'sys', 'sys', 0);
    InputStream in = new ClassPathResource("快猫button_v20191008.xls").getInputStream();
    HSSFWorkbook workbook = new HSSFWorkbook(in);
    HSSFSheet sheet = workbook.getSheetAt(4);
    int maxRow = sheet.getLastRowNum();
    /**插入权限表*/
    for (int i = 1; i < maxRow + 1; i++) {
      StringBuilder sql = new StringBuilder("INSERT INTO quixmart_business.b_merchant_permission (parent_id, permission_code, permission_name, permission_type, create_time, update_time, create_by, update_by, deleted) VALUES (");
      HSSFRow row = sheet.getRow(i);
      Double parentId = row.getCell(1).getNumericCellValue();
      String permissionName = row.getCell(3).getStringCellValue();
      String permissionCode = row.getCell(4).getStringCellValue();
      sql.append(parentId.intValue())
        .append("," + "'"+permissionCode+"'")
        .append("," + "'" + permissionName + "'")
        .append("," + "'" + permissionType + "'")
        .append("," + createTime)
        .append("," + updateTime)
        .append("," + "'" + createBy + "'")
        .append("," + "'" + updateBy + "'")
        .append("," + deleted)
        .append(endFlag);
      System.out.println(sql);
    }

    System.out.println("******************************************角色和权限关系表********************************************************");
/*    int templateMerchantId = 0;
    int adminRoleId = 4;
*//**插入角色权限关系表*//*
//INSERT INTO quixmart_business.b_merchant_role_permission (id, merchant_id, role_id, permission_id, create_time, update_time, create_by, update_by, deleted) VALUES (90619, 0, 4, 50, '2018-08-29 23:07:59', '2018-08-29 23:07:59', 'sys', 'sys', 0);
    for (int i = 1; i < maxRow + 1; i++) {
      StringBuilder sql = new StringBuilder("INSERT INTO quixmart_business.b_merchant_role_permission (merchant_id, role_id, permission_id, create_time, update_time, create_by, update_by, deleted) VALUES (");
      HSSFRow row = sheet.getRow(i);
      Double permissionId = row.getCell(4).getNumericCellValue();
      sql.append(templateMerchantId)
        .append("," + adminRoleId)
        .append("," + permissionId.intValue())
        .append("," + createTime)
        .append("," + updateTime)
        .append("," + "'" + createBy + "'")
        .append("," + "'" + updateBy + "'")
        .append("," + deleted)
        .append(endFlag);
      System.out.println(sql);
    }*/

  }
}
